// goods  api
const Service = require('egg').Service;
const source = require("../public/source.js");
const brands = require("../public/brands.js");
const string = require("./get/string.js");
const array = require("./get/array.js");
// const dicts=require("../public/dicts.js");

class GoodsService extends Service {
    //某个用户的指定交易状态的总数
    async dealsTotal(tel) {
        let sql = `select d.status,count(*) total from deal d INNER JOIN user u ON d.userId=u.id WHERE tel='${tel}' and d.status in (0,1,2,3) GROUP BY d.status;`;
        let end = await this.app.mysql.query(sql);
        let dealsTotal = { waitPay: 0, waitSend: 0, waitGet: 0, waitComment: 0 };
        end.forEach((item) => {
            switch (item.status) {
                case 0: dealsTotal["waitPay"] = item.total; break;
                case 1: dealsTotal["waitSend"] = item.total; break;
                case 2: dealsTotal["waitGet"] = item.total; break;
                case 3: dealsTotal["waitComment"] = item.total; break;
            }
        })
        return dealsTotal;
    }
    //最新上市的手机
    async newest(brandId, limit = 20) {
        let whereStr = array.toSqlWhereStr([["status", 1], ["baseBrand", brandId]]);
        let sql = `select id,imgs from goods ${whereStr} ORDER BY marketTime desc limit ${limit}`;
        let rows = await this.app.mysql.query(sql);
        rows.map((item)=>{
            item.imgs=`${this.app.baseUrl}/public/img/goods/${item.imgs.split(";")[0]}`;
        });
        let arr=[]
        for(let i=0;i<4;i++){
            let tempArr=[];
            for(let j=0;j<5;j++){
                tempArr.push(rows[j]);
            }
            arr.push(tempArr);
        }
        return arr;
    }
    //最热商品：days 多少天以内的最热商品
    async hotest(days, brandId, limit = 5) {
        let whereStr = array.toSqlWhereStr([["d.status", "in", "(1,2,3,4)"], ["TIMESTAMPDIFF(DAY, d.startTime, NOW())", "<=", days], ["baseBrand", brandId]]);
        let sql = `SELECT g.id,g.imgs,count(*) as sellTotal from deal d INNER JOIN deal_list dl ON d.id=dl.dealId INNER JOIN goods g ON g.id=dl.goodsId ${whereStr} GROUP BY dl.goodsId ORDER BY sellTotal desc LIMIT ${limit};`;
        let rows = await this.app.mysql.query(sql);
        rows.map((item)=>{
            item.imgs=`${this.app.baseUrl}/public/img/goods/${item.imgs.split(";")[0]}`;
        })
        return rows;
    }
    //limit 5,10：检索记录行 6-15  
    //order by： DESC(降序)、ASC(升序,mysql默认值)
    //orderBy  参考opts.orderType   1按上市时间2按销售价格3按销售热度
    async byBrandSeries(brandId, seriesId, currPage = 0, pageSize = 10, orderBy = 1, orderType = "desc") {
        if(seriesId==0) seriesId=undefined;
        let where = [["g.baseBrand", brandId], ["g.baseSeries", seriesId], ["g.status", 1]];
        let end=await this.pageData(where, currPage, pageSize, orderBy, orderType);
        brandId && (end.brandId=brandId);
        seriesId && (end.seriesId=seriesId);
        orderBy && (end.orderBy=orderBy);
        return end;
    }
    //商品详情
    async detail(goodsId) {
        let sql = `SELECT title,imgs,GROUP_CONCAT(price ORDER BY price) price,GROUP_CONCAT(DISTINCT ram ORDER BY ram) ram,GROUP_CONCAT(DISTINCT rom ORDER BY rom) rom, GROUP_CONCAT(DISTINCT color) color,colorImg FROM goods g INNER JOIN goods_style gs ON g.id=gs.goodsId WHERE goodsId=${goodsId};`;
        let rows = await this.app.mysql.query(sql);
        rows[0].colorImg = `${this.app.baseUrl}/public/img/goods/${rows[0].colorImg}`;
        let totalSell = await this.sellAmount(goodsId);
        let monthSell = await this.sellAmount(goodsId, 30);
        let goodComment = await this.commentAmount(goodsId, [4, 5]);
        let totalComment = await this.commentAmount(goodsId);
        let goodRatio = totalComment == 0 ? "0%" : Math.round(goodComment / totalComment * 100) + "%";
        return { ...rows[0], totalSell, monthSell, goodRatio, totalComment };
    }
    //商品规格参数
    async params(goodsId) {
        let baseSql = `SELECT * FROM goods WHERE id=${goodsId};`;
        let styleSql = `SELECT GROUP_CONCAT(DISTINCT ram ORDER BY ram) rams,GROUP_CONCAT(DISTINCT rom ORDER BY rom) roms from goods_style WHERE goodsId=${goodsId};`;
        let baseRows = await this.app.mysql.query(baseSql);
        let styleRows = await this.app.mysql.query(styleSql);
        let baseInfo = baseRows[0];
        let styleInfo = styleRows[0];
        let brand = brands.filter(item => item.id == baseInfo.baseBrand)[0];
        let camera = [{
            name: "前置摄像头",
            cont: baseInfo.cameraFront + "万像素"
        }, {
            name: "后摄像头",
            cont: baseInfo.cameraBehind + "万像素"
        }]
        let show = [{
            name: "分辨率",
            cont: baseInfo.showResolution + "像素"
        }, {
            name: "屏幕尺寸",
            cont: baseInfo.showSize + "英寸"
        }]
        let network = [{
            name: "网络模式",
            cont: baseInfo.networkMode == 1 ? "双卡双待" : "不详"
        }, {
            name: "网络类型",
            cont: baseInfo.networkType == 1 ? "SA/NSA双模(5G)" : "不详"
        }]
        let base = [{
            name: "电池容量",
            cont: baseInfo.baseBattery + "mAh"
        }, {
            name: "品牌",
            cont: brand.name
        }, {
            name: "机身颜色",
            cont: baseInfo.baseColor
        }, {
            name: "CPU品牌",
            cont: baseInfo.baseCpuBrand
        }, {
            name: "CPU核心数",
            cont: baseInfo.baseCpuCores
        }, {
            name: "产品名称",
            cont: baseInfo.baseName
        }, {
            name: "系列",
            cont: brand.series.filter(item => item.id == baseInfo.baseSeries)[0].name
        }, {
            name: "上市时间",
            cont: baseInfo.marketTime
        }]
        let store = [{
            name: "内存RAM(GB)",
            cont: styleInfo.rams.replace(/,/g, ";")
        }, {
            name: "辅存ROM(GB)",
            cont: styleInfo.roms.replace(/,/g, ";")
        }]
        let params = [
            { title: "拍照", data: camera },
            { title: "显示", data: show },
            { title: "网络", data: network },
            { title: "基本", data: base },
            { title: "存储", data: store },
        ]
        return params;
    }
    //商品评价
    async comment(goodsId, currPage = 0, pageSize = 10, stars = null, hasCont = null, hasImg = null) {   //stars: [] , hasCont: boolean, hasImg: boolean
        let starsStr = stars === null ? "" : `And stars in (${stars.toString()})`;
        let hasContStr = hasCont === null ? "" : `AND c.type=${hasCont ? 1 : 0}`;
        let hasImgStr = hasImg === null ? "" : `AND ${hasImg ? "c.imgs like '%.jpg%'" : "c.imgs=''"}`;
        let rowsSql = `SELECT d.userId,u.avatar,u.nickname,g.baseName,gs.ram,gs.rom,gs.color,c.stars,c.content,c.time,c.readAmount,c.imgs FROM comment c INNER JOIN deal_list dl ON c.dealListId=dl.id INNER JOIN deal d ON d.id=dl.dealId INNER JOIN user u ON u.id=d.userId INNER JOIN goods g ON g.id=dl.goodsId INNER JOIN goods_style gs ON gs.goodsId=g.id WHERE g.id=${goodsId} ${starsStr} ${hasContStr} ${hasImgStr} ORDER BY c.time desc limit ${currPage},${pageSize}`;
        let avgSql = `SELECT FORMAT(ROUND(AVG(stars)),1) avgStars FROM comment c INNER JOIN deal_list dl ON c.dealListId=dl.id WHERE goodsId=${goodsId};`;
        let totalSql = `SELECT count(*) total FROM comment c INNER JOIN deal_list dl ON c.dealListId=dl.id INNER JOIN deal d ON d.id=dl.dealId INNER JOIN user u ON u.id=d.userId INNER JOIN goods g ON g.id=dl.goodsId INNER JOIN goods_style gs ON gs.goodsId=g.id WHERE g.id=${goodsId} ${starsStr} ${hasContStr} ${hasImgStr};`;
        let starsCountSql = `SELECT stars,count(stars) total FROM comment c INNER JOIN deal_list as dl on c.dealListId=dl.id WHERE dl.goodsId=${goodsId} GROUP BY stars ORDER BY stars;`;
        let rowsEnd = await this.app.mysql.query(rowsSql);
        rowsEnd=rowsEnd.map((item)=>{
            item.avatar=`${this.app.baseUrl}/public/img/avatar/${item.avatar}`;
            item.imgs=`${this.app.baseUrl}/public/img/goods/${item.imgs}`;
            return item;
        })
        let avgEnd = await this.app.mysql.query(avgSql);
        let totalEnd = await this.app.mysql.query(totalSql);
        let starsCountEnd = await this.app.mysql.query(starsCountSql);
        let starsCount = [{ stars: 0, total: 0 }, { stars: 1, total: 0 }, { stars: 2, total: 0 }, { stars: 3, total: 0 }, { stars: 4, total: 0 }, { stars: 5, total: 0 }]
        starsCountEnd.forEach((item, ind) => {
            starsCount[item.stars].total = item.total;
        })
        let data = {
            avgStars: avgEnd[0].avgStars,
            rows: rowsEnd,
            total: totalEnd[0].total,
            currPage,
            pageSize,
            starsCount,
        };
        return data;
    }
    //目前只支持根据手机传播名来搜索
    //简单版搜索引擎参考网址：https://blog.csdn.net/miaowei_/article/details/110704654?utm_term=mysql
    //orderBy: 1按上市时间2按销售价格3按销售热度
    async searchByWords(content, currPage = 0, pageSize = 10, orderBy = 1, orderType = "desc") {
        let names = this.app.goodsNames;
        let matchEnd = null;
        content = content.toLocaleLowerCase().replace(/ /g, "");
        for (let i = 0; i < names.length; i++) {
            if (content.length < names[i].length) {
                let end = names[i].match(content);
                end && (matchEnd = end);
                if (matchEnd) break;
            } else {
                let end = content.match(names[i]);
                end && (matchEnd = end);
                if (matchEnd && content === names[i]) break;
            }
        }
        if (matchEnd) {
            let rowsSql = `SELECT * FROM goods g INNER JOIN goods_style gs ON g.id=gs.goodsId WHERE replace(LOWER(g.baseName),' ','') like LOWER(CONCAT('%','${matchEnd[0]}','%')) 
            and g.status=1 group by g.id order by ${orderBy == 2 ? "price" : "marketTime"} ${orderType} limit ${currPage},${pageSize}`;
            let rowsEnd = await this.app.mysql.query(rowsSql);
            let total = await this.service.get.db.total("goods;g", { where: [["replace(LOWER(g.baseName),' ','')", "like", `LOWER(CONCAT('%','${matchEnd[0]}','%'))`]] });
            return { rows: rowsEnd, total };
        } else {
            return { rows: [], total: 0, currPage, pageSize };
        }
    }
    //根据选项搜索商品
    async searchByOpts(brandId, price, screen, ram, rom, currPage, pageSize, orderBy, orderType) {
        let where = [["baseBrand", brandId]];
        price && where.push(...string.toSqlWhereArr("price", price));
        screen && where.push(...string.toSqlWhereArr("screen", screen));
        ram && where.push(...string.toSqlWhereArr("ram", ram));
        rom && where.push(...string.toSqlWhereArr("rom", rom));
        return await this.pageData(where, currPage, pageSize, orderBy, orderType);;
    }
    async shopCart(userId, currPage = 0, pageSize = 10) {
        let sql = `SELECT g.id,substring_index(g.imgs,';',1) img,g.title,sc.setMeal,sc.amount,gs.color,gs.ram,gs.rom,gs.price FROM shop_cart sc INNER JOIN goods_style gs ON sc.goodsStyleId=gs.id INNER JOIN goods g ON g.id=gs.goodsId WHERE userId=${userId} ORDER BY sc.time desc limit ${currPage},${pageSize};`;
        let rows = await this.app.mysql.query(sql);
        return rows;
    }
    //添加至购物车
    async shopCartAdd(userId, goodsStyleId, setMeal, amount = 1) {
        let end = await this.app.mysql.get("shop_cart", { userId, goodsStyleId, setMeal });  //获取一条数据，直接返回一个对象
        let time = this.app.mysql.literals.now;
        if (end) {
            end = await this.app.mysql.update('shop_cart', {
                id: end.id,
                amount: end.amount + amount,
                time
            });
        } else {
            end = await this.app.mysql.insert('shop_cart', { userId, goodsStyleId, setMeal, amount, time });
        }
        return end;
    }
    //移出购物车
    //id: number 或 array
    //type: 0整个删除1逐个减少
    async shopCartOut(id, type = 1) {
        let end = null, sql = "";
        if (type === 0) {
            sql = `DELETE FROM shop_cart WHERE id in (${id.toString()});`;
        } else {
            sql = `UPDATE shop_cart SET amount = case when amount>1 then amount-1 else 1 end WHERE id = ${id}`;
        }
        end = await this.app.mysql.query(sql);
        return end;
    }
    //交易中的商品信息
    //status(交易状态)： 0待付款；1待发货；2待收货；3待评价；4已完成交易；5已关闭交易
    async deal(userId, status, currPage = 0, pageSize = 10) {
        let dealSql = `SELECT d.id,GROUP_CONCAT(dl.goodsId ORDER BY d.startTime desc separator ';') goodsIds FROM deal d INNER JOIN deal_list dl ON d.id=dl.dealId WHERE d.userId=${userId} and d.status=${status} GROUP BY d.id ORDER BY d.startTime desc limit ${currPage},${pageSize};`;
        let dealRows = await this.app.mysql.query(dealSql);
        let dealArr = [];
        for (let i = 0; i < dealRows.length; i++) {
            let listArr = [];
            let deal = dealRows[i];
            let goodsIds = deal.goodsIds.split(";");
            for (let j = 0; j < goodsIds.length; j++) {
                let sql = `SELECT g.title,substring_index(g.imgs,';',1) img,dl.setMeal,dl.color,dl.ram,dl.rom,dl.amount,dl.unitPrice,d.note FROM deal d INNER JOIN deal_list dl ON dl.dealId=d.id INNER JOIN goods g ON g.id=dl.goodsId WHERE d.userId=${userId} AND d.status=${status} and d.id=${deal.id} AND g.id=${goodsIds[j]} limit ${currPage},${pageSize};`;
                let end = await this.app.mysql.query(sql);
                listArr.push(end[0])
            }
            let listObj = {
                dealId: deal.id,
                lists: listArr,
            }
            dealArr.push(listObj);
        }
        let totalSql = `SELECT COUNT(DISTINCT d.id) total FROM deal d INNER JOIN deal_list dl ON d.id=dl.dealId WHERE d.userId=${userId} and d.status=${status};`;
        let totalEnd = await this.app.mysql.query(totalSql);
        return { rows: dealArr, total: totalEnd[0].total };
    }







    //销售数量
    //days指定多少天内的销量
    async sellAmount(goodsId, days) {
        let daysStr = days ? `AND TIMESTAMPDIFF(DAY, d.startTime, NOW())<=${days}` : "";
        let sql = `SELECT count(*) as amount from deal d INNER JOIN deal_list dl ON d.id=dl.dealId WHERE d.status in (1,2,3,4) ${daysStr} and dl.goodsId=${goodsId};`;
        let end = await this.app.mysql.query(sql);
        return end[0] ? end[0].amount : 0;
    }
    //评价数量
    async commentAmount(goodsId, stars) {  //stars: []
        let starsStr = stars ? `and c.stars in (${stars.toString()})` : "";
        let sql = `SELECT count(dl.goodsId) as amount FROM comment c INNER JOIN deal_list dl ON c.dealListId=dl.id WHERE dl.goodsId=${goodsId} ${starsStr} GROUP BY dl.goodsId;`;
        let end = await this.app.mysql.query(sql);
        return end[0] ? end[0].amount : 0;
    }
    //指定商品的价格
    async price(goodsId) {
        let sql = `SELECT price from goods_style WHERE goodsId=${goodsId} GROUP BY ram,rom ORDER BY price limit 1`;
        let end = await this.app.mysql.query(sql);
        return end[0].price;
    }
    //获取指定商品数据
    // where:[[key,val,operator],[key,val,operator],...]
    // {g.baseBrand: brandId, g.baseSeries: seriesId, g.status:1}
    async pageData(where, currPage = 0, pageSize = 10, orderBy = 1, orderType = "desc") {
        let sql = "";
        let whereStr=array.toSqlWhereStr(where);
        let { app, service } = this;
        if (orderBy != 3) {
            sql = `SELECT DISTINCT g.id,title,substring_index(g.imgs,';',1) imgs FROM goods g INNER JOIN goods_style gs ON g.id=gs.goodsId ${whereStr} ORDER BY ${orderBy == 1 ? "marketTime" : "price"} ${orderType} limit ${currPage},${pageSize};`;
        } else {
            sql = `SELECT DISTINCT g.id,title,substring_index(g.imgs,';',1) imgs from goods g INNER JOIN goods_style gs ON g.id=gs.goodsId INNER JOIN (SELECT dl.goodsId,count(*) sellTotal from deal d INNER JOIN deal_list dl ON d.id=dl.dealId WHERE d.status in (1,2,3,4) GROUP BY dl.goodsId) un ON g.id=un.goodsId ${whereStr} ORDER BY sellTotal ${orderType} LIMIT ${currPage},${pageSize};`;
        }
        let rows = await app.mysql.query(sql);
        let totalSql = `SELECT COUNT(DISTINCT g.id) total FROM goods g INNER JOIN goods_style gs ON g.id=gs.goodsId ${whereStr}`;
        let totalEnd = await this.app.mysql.query(totalSql)
        for (let i = 0; i < rows.length; i++) {
            let row = rows[i];
            row.imgs = `${this.app.baseUrl}/public/img/goods/${row.imgs}`;
            row.monthSell = await this.sellAmount(row.id, 30);
            row.totalSell = await this.sellAmount(row.id);
            row.isNew = await service.get.db.boolean("goods", `TIMESTAMPDIFF(DAY, marketTime, NOW())<=${source.number.newGoodsDays}`, { where: [["id", row.id]] });
            row.isHot = row.monthSell > source.number.hotGoodsNum;
            row.totalComment = await this.commentAmount(row.id);
            let goodComment = await this.commentAmount(row.id, [4, 5]);
            row.goodRatio = row.totalComment == 0 ? "0%" : Math.round(goodComment / row.totalComment * 100) + "%";
            row.price = await this.price(row.id);
        };
        return { rows, total: totalEnd[0].total, currPage, pageSize }
    }
}
module.exports = GoodsService;